package fr.umlv.m2.recipes.ideas.dao;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import fr.umlv.m2.recipes.ideas.bean.RecipeToKeywordBean;
import fr.umlv.m2.recipes.ideas.dao.interfaces.RecipesRelativeDAOInterface;
import fr.umlv.m2.recipes.ideas.table.enumerations.TableName;

/**
 * DAO uses to handle a liaison between a recipe and a keyword
 * @author Christophe Chillan
 *
 */
public class RecipeToKeywordDAO implements RecipesRelativeDAOInterface<RecipeToKeywordBean> {
	
	private static final String TABLE_RECIPESTOKEYWORD = TableName.RECIPESTOKEYWORD.toString();
	private static final String COL_ID_RECIPES = "RECIPES_ID";
	private static final String COL_ID_KEYWORD = "KEYWORD_ID";
	private static final String FOREIGN_RECIPES_ID_REFERENCES="ID";
	private static final String FOREIGN_KEYWORD_ID_REFERENCES="ID";
	private static final int NUM_COL_COUNT = 0;
	
	private final StringBuilder CREATE_DB;
	
	/**
	 * Create a recipe to keyword DAO
	 */
	public RecipeToKeywordDAO(){
		CREATE_DB = new StringBuilder();
		CREATE_DB.append("CREATE TABLE ");
		CREATE_DB.append(TABLE_RECIPESTOKEYWORD).append(" ");
		CREATE_DB.append("(");
		CREATE_DB.append(COL_ID_RECIPES).append(" INTEGER, ");
		CREATE_DB.append(COL_ID_KEYWORD).append(" INTEGER, ");
		CREATE_DB.append("PRIMARY KEY(").append(COL_ID_RECIPES).append(" , ");
		CREATE_DB.append(COL_ID_KEYWORD).append(") , ");
		CREATE_DB.append("FOREIGN KEY(").append(COL_ID_RECIPES).append(") ");
		CREATE_DB.append(" REFERENCES ").append(TableName.RECIPES.toString()).append("(");
		CREATE_DB.append(FOREIGN_RECIPES_ID_REFERENCES).append("), ");
		CREATE_DB.append("FOREIGN KEY(").append(COL_ID_KEYWORD).append(") ");
		CREATE_DB.append(" REFERENCES ").append(TableName.STEP.toString()).append("(");
		CREATE_DB.append(FOREIGN_KEYWORD_ID_REFERENCES).append(")");
		CREATE_DB.append(");");
	}
	

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_DB.toString());

	}

	/**
	 * @see SQLiteOpenHelper
	 */
	public void onUpgrade(SQLiteDatabase db) {
		db.execSQL("DROP TABLE "+ TABLE_RECIPESTOKEYWORD);
		onCreate(db);	

	}

	/**
	 * Insert a recipe to keyword entry in the database
	 *  @param db
	 *  @param bean
	 *  @return long - 1 if the entry has been inserted, 0 otherwise 
	 */
	public long insert(SQLiteDatabase db, RecipeToKeywordBean bean) {

		ContentValues values = new ContentValues();
		values.put(COL_ID_RECIPES, bean.getRecipeId());
		values.put(COL_ID_KEYWORD, bean.getKeywordId());
		return db.insert(TABLE_RECIPESTOKEYWORD, null, values);
	}

	/**
	 * remove all the entries with the specified id
	 * @return integer - the number of entries removed
	 */
	public int removeWithId(SQLiteDatabase db, int id) {
		return db.delete(TABLE_RECIPESTOKEYWORD, COL_ID_RECIPES+" = "+id+" OR "+ COL_ID_KEYWORD+" = "+id, null);
	}

	/**
	 * remove the relation defined with the id couple in parameter
	 * @return integer - the number of entries removed
	 */
	public int removeTheRelation(SQLiteDatabase db, int recipesId, int keywordId) {
		return db.delete(TABLE_RECIPESTOKEYWORD, COL_ID_RECIPES+" = "+recipesId+" AND "+ COL_ID_KEYWORD+" = "+keywordId, null);
	}
	
	/**
	 * Get the number of recipe to keyword entries in the databases
	 * @param db
	 * @return the number of recipe to keyword entries
	 */
	public int getCountInstances(SQLiteDatabase db) {
		StringBuilder query = new StringBuilder();
		query.append("SELECT count(*) ");
		query.append("from ").append(TABLE_RECIPESTOKEYWORD);
		Cursor c = db.rawQuery(query.toString(), null);
		return cursorToCount(c);
	}
	
	private int cursorToCount(Cursor c){
		if(c.getCount()==0){
			return 0;
		}
		c.moveToFirst();
		return c.getInt(NUM_COL_COUNT);
	}

}
